﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using uMES.LeanManufacturing.DBUtility;
using System.Data;
using uMES.LeanManufacturing.ParameterDTO;
//using System.Data.OracleClient;
using uMESExternalControl.ToleranceInputLib;
using System.Drawing;
using System.Web.SessionState;
using System.Web;
using Oracle.ManagedDataAccess.Client;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESPartReportingBusiness
    {
        uMESCommonBusiness common = new uMESCommonBusiness();


        #region 图文档打印信息申请
        public uMESPagingDataDTO GeuMESFigureDocumentPrinttSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D2(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        protected string GetSQL_D2(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@" SELECT DISTINCT  pb.productname,p.description,p.productrevision,NVL(mo.processno,' ') AS processno,
                                       c.productid,NVL(f.createtimes,0) createtimes,f.id
                                FROM container c
                                LEFT JOIN containeroperatehistoryinfo ch ON ch.containerid = c.containerid AND ch.iscurrentworkflow=1
                                LEFT JOIN product p ON p.productid = c.productid
                                LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                                LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid
                                LEFT JOIN figureDocumentPrintingInfo f ON f.productid = c.productid AND NVL(mo.processno,' ') = NVL(f.processno,' ')
                                WHERE 1 = 1 and c.status=1 AND  c.finishstate IS NULL AND c.qty >0
                                AND c.parentcontainerid IS NULL
                                AND EXISTS 
                                (
                                SELECT DISTINCT ws.workflowid
                                FROM Workflowstep ws 
                                LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                                LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,s.specid)
                                WHERE s.issynergic = 1 AND ch.workflowid = ws.workflowid)");


            if (para.Keys.Contains("MfgManagerid"))
            {
                if (!string.IsNullOrEmpty(para["MfgManagerid"]))
                {
                    strQuery.AppendLine(string.Format("AND mo.mfgmanagerid = '{0}'", para["MfgManagerid"]));
                }
            }


            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }

            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName2") && !string.IsNullOrEmpty(para["ProductName2"])) //图号/名称
            {
                strQuery.AppendLine(string.Format("AND pb.productname='{0}' ", para["ProductName2"]));

            }

            strQuery.AppendLine(" ORDER BY pb.productname ASC");

            return strQuery.ToString();
        }
        #endregion

        #region 零件进度表

        #region 获取零件进度表主信息
        public uMESPagingDataDTO GetPartReportingMainInfo(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT DISTINCT ch.containerid,ch.containername,ch.processno,ch.oprno,ch.productname,");
            strQuery.AppendLine("       ch.description,ch.mfgordername,ch.conqty,ch.originalstartdate,ws.workflowid,c.productid,");
            strQuery.AppendLine("       c.holdreasonid,NVL(c.state,0) AS conState,c.status");
            strQuery.AppendLine("FROM containeroperatehistoryinfo ch");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = ch.containerid");
            strQuery.AppendLine("LEFT JOIN currentstatus cus ON cus.currentstatusid = c.currentstatusid");
            strQuery.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowstepid = cus.workflowstepid");
            strQuery.AppendLine("WHERE 1 = 1");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(ch.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(ch.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(ch.productname) LIKE '%{0}%' OR LOWER(ch.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductID")) //ProductID
            {
                if (!string.IsNullOrEmpty(para["ProductID"]))
                {
                    strQuery.AppendLine(string.Format(" AND ch.productid='{0}' ", para["ProductID"]));
                }
            }

            if (para.Keys.Contains("ContainerIDList")) //ContainerIDList
            {
                if (!string.IsNullOrEmpty(para["ContainerIDList"]))
                {
                    strQuery.AppendLine(string.Format(" AND ch.containerid IN ({0}) ", para["ContainerIDList"]));
                }
            }

            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.planstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.planstartdate  <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(" AND EXISTS (SELECT DISTINCT ch1.containerid ");
                    strQuery.AppendLine("FROM containeroperatehistoryinfo ch1 ");
                    strQuery.AppendLine("WHERE ch1.containerid = ch.containerid ");
                    strQuery.AppendLine(string.Format("AND LOWER(ch1.specname) LIKE '%{0}%')", para["SpecName"].ToLower()));
                }
            }

            strQuery.AppendLine("ORDER BY ch.productname ASC ");

            uMESPagingDataDTO result = OracleHelper.GetPagingDataIns(strQuery.ToString(), intPageIndex, intPageSize);

            DataTable dtMain = result.DBTable.Copy();
            //跟踪卡主信息
            dtMain.TableName = "containerName";//update:Wangjh result.DBTable.TableName->dtMain.TableName
            result.DBset.Tables.Add(dtMain);

            //跟踪卡ID
            string strConIDList = "'',";
            if (dtMain.Rows.Count > 0)
            {
                for (int i = 0; i < dtMain.Rows.Count; i++)
                {
                    strConIDList += "'" + dtMain.Rows[i]["containerid"].ToString() + "',";
                }
            }
            strConIDList = strConIDList.TrimEnd(',');
            //查询详细信息

            strQuery = new StringBuilder();
            strQuery.AppendLine(@"select cp.*,decode(cp.workflowid,ws.workflowid,1,0) iscurwf,s.issynergic,w.workflowrevision,w.workflowrevision 
                                from containeroperatehistoryinfo cp 
                                left join container c on c.containerid=cp.containerid
                                left join currentstatus cu on cu.currentstatusid=c.currentstatusid
                                left join workflowstep ws on ws.workflowstepid=cu.workflowstepid 
                                left join workflow w on w.workflowid = cp.workflowid
                                left join spec s on s.specid=cp.specid ");
            strQuery.AppendLine(" where cp.containerid IN (" + strConIDList + ")");
            strQuery.AppendLine(" order by decode(cp.workflowid,ws.workflowid,1,0) ,cp.workflowid,cp.sequenceno ");

            DataTable dtDetail = OracleHelper.GetDataTable(strQuery.ToString());
            if(dtDetail.Rows.Count>0)
               // dtDetail = dtDetail.Select("iscurwf=1").CopyToDataTable();//先暂时处理为只看当前工艺

            dtDetail.Columns.Add("SpecNameNew");
            dtDetail.Columns.Add("state");
            dtDetail.Columns.Add("synerspec");
            for (int i = 0; i < dtDetail.Rows.Count; i++)
            {
                string strMainConId = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["maincontainerid"].ToString()))
                {
                    strMainConId = dtDetail.Rows[i]["maincontainerid"].ToString();
                }

                if (strConIDList.Contains(",'" + strMainConId + "'") == false)
                {
                    strConIDList += ",'" + strMainConId + "'";
                }
            }
            strConIDList = strConIDList.TrimEnd(',');
            if (para.ContainsKey("ContainerIDList"))
            {
                para.Remove("ContainerIDList");
            }
            para.Add("ContainerIDList", strConIDList);

            //如果有specname，会导致查不出工序。页面没颜色
            if (para.ContainsKey("SpecName")) {
                para.Remove("SpecName");
            }

            //获取派工信息
            DataTable dtDispatch = GetSpecDispatchInfo(para);
            //获取报工信息
            DataTable dtReport = GetSpecReportInfo(para);
            //获取检验信息
            DataTable dtCheck = GetSpecCheckInfo(para);
            //获取质量记录信息
            DataTable dtQual = GetSavedQualInfo(para);
            //获取完工确认信息
            DataTable dtFinish = GetContainerSpecFinishInfo(para);

            //获取外协审批信息
            DataTable dtSynAudit = GetSynerAuditInfo(para);

            for (int i = 0; i < dtDetail.Rows.Count; i++)
            {
                string strName = dtDetail.Rows[i]["SpecName"].ToString();
                dtDetail.Rows[i]["SpecNameNew"] = common.GetSpecNameWithOutProdName(strName);

                //批次ID
                string strConID = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["containerid"].ToString()))
                {
                    strConID = dtDetail.Rows[i]["containerid"].ToString();
                }

                //工艺规程
                string strWorkFlowID = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["workflowid"].ToString()))
                {
                    strWorkFlowID = dtDetail.Rows[i]["workflowid"].ToString();
                }

                //工序ID
                string strSpecID = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["specid"].ToString()))
                {
                    strSpecID = dtDetail.Rows[i]["specid"].ToString();
                }

                // 父批次ID
                string strMainConId = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["maincontainerid"].ToString()))
                {
                    strMainConId = dtDetail.Rows[i]["maincontainerid"].ToString();
                }

                //是否跳序加工 
                string strIsSkiped = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["isskiped"].ToString()))
                {
                    strIsSkiped = dtDetail.Rows[i]["isskiped"].ToString();
                }

                //协作工序
                string strIsSynergic = string.Empty;
                if (!string.IsNullOrEmpty(dtDetail.Rows[i]["issynergic"].ToString()))
                {
                    strIsSynergic = dtDetail.Rows[i]["issynergic"].ToString();
                }



                string strFilterConID = strConID;
                if (strMainConId != "" & strIsSkiped != "1")
                {
                    strFilterConID = strMainConId;
                }
                string strFilter = string.Format("containerid='{0}' AND workflowid ='{1}' AND specid='{2}' ", strFilterConID, strWorkFlowID, strSpecID);

                if (strIsSynergic == "1")
                {
                    dtDetail.Rows[i]["synerspec"] = "1";
                }
                else
                {
                    DataRow[] drSynAudit = dtSynAudit.Select(strFilter);
                    if (drSynAudit.Length > 0)
                    {
                        dtDetail.Rows[i]["synerspec"] = "2";
                    }
                }

                DataRow[] drDispatch = dtDispatch.Select(strFilter + "AND dispatchtype=1");
                if (drDispatch.Length > 0)
                {
                    dtDetail.Rows[i]["state"] = "1";
                }

                DataRow[] drReceived = dtDispatch.Select(strFilter + "AND dispatchtype=1 AND (Status=20 or status=25)");
                if (drReceived.Length > 0)
                {
                    dtDetail.Rows[i]["state"] = "5";
                }

                DataRow[] drReport = dtReport.Select(strFilter + "AND ReportType IN ('0','1','3')");
                if (drReport.Length > 0)
                {
                    dtDetail.Rows[i]["state"] = "2";
                }

                DataRow[] drCheck = dtCheck.Select(strFilter);
                if (drCheck.Length > 0)
                {
                    dtDetail.Rows[i]["state"] = "3";
                }

                DataRow[] drFinish = dtFinish.Select(strFilter);
                if (drFinish.Length > 0)
                {
                    dtDetail.Rows[i]["state"] = "4";
                }

                DataRow[] drQual = dtQual.Select(strFilter);
                if (drQual.Length > 0)
                {
                    dtDetail.Rows[i]["IsQuality"] = "1";
                }
            }
            dtDetail.TableName = "detailinfo";//update:Wangjh result.DBTable.TableName->dtDetail.TableName
            result.DBset.Tables.Add(dtDetail);
            return result;
        }

        #endregion


        /// <summary>
        /// 获取外协审批信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSynerAuditInfo(Dictionary<string, string> para)
        {
            DataTable dtReturn = new DataTable();

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT st.containerid,st.workflowid,ws.sequence startseq,ws2.sequence endseq ,s.unitworktime,s.setupworktime 
                        FROM  synergicinfo st 
                        LEFT JOIN spec s ON s.specid=st.specid
                        LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid
                        LEFT JOIN workflowstep ws ON ws.workflowid=st.workflowid AND (ws.specbaseid=sb.specbaseid OR ws.specid=s.specid)
                        LEFT JOIN spec s2 ON s2.specid=st.returnspecid
                        LEFT JOIN specbase sb2 ON sb2.specbaseid=s2.specbaseid
                        LEFT JOIN workflowstep ws2 ON ws2.workflowid=st.workflowid AND (ws2.specbaseid=sb2.specbaseid OR ws2.specid=s2.specid)
                        WHERE st.isuse =0 AND st.status = 5
                        AND st.containerid IN (" + strConIDList + @")");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            dt.Columns.Add("specid");
            dtReturn = dt.Clone();
            string strWorkFlowIDList = string.Empty;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string strFLowID = string.Empty;
                if (!string.IsNullOrEmpty(dt.Rows[i]["workflowid"].ToString()))
                {
                    strFLowID = dt.Rows[i]["workflowid"].ToString();
                }

                if (strWorkFlowIDList.Contains(",'" + strFLowID + "'") == false)
                {
                    strWorkFlowIDList += "'" + strFLowID + "',";
                }
            }

            if (strWorkFlowIDList != "")
            {
                strWorkFlowIDList = strWorkFlowIDList.TrimEnd(',');

                DataTable dtFolw = GetSpecInfo(strWorkFlowIDList);

                if (dt.Rows.Count > 0)
                {

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string strContainerId = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[i]["containerid"].ToString()))
                        {
                            strContainerId = dt.Rows[i]["containerid"].ToString();
                        }

                        string strFLowID = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[i]["workflowid"].ToString()))
                        {
                            strFLowID = dt.Rows[i]["workflowid"].ToString();
                        }

                        string strStartSeq = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[i]["startseq"].ToString()))
                        {
                            strStartSeq = dt.Rows[i]["startseq"].ToString();
                        }

                        string strEndSeq = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[i]["endseq"].ToString()))
                        {
                            strEndSeq = dt.Rows[i]["endseq"].ToString();
                        }

                        DataRow[] dr = dtFolw.Select(string.Format("workflowid='{0}' and sequence>={1} and sequence<={2}", strFLowID, strStartSeq, strEndSeq));
                        if (dr.Length > 0)
                        {
                            for (int h = 0; h < dr.Length; h++)
                            {
                                DataRow row = dtReturn.NewRow();
                                row["containerid"] = strContainerId;
                                row["workflowid"] = strFLowID;
                                row["specid"] = dr[h]["specid"].ToString();
                                dtReturn.Rows.Add(row);
                            }
                        }
                    }


                }
            }
            return dtReturn;
        }

        public DataTable GetSpecInfo(string strWorkFlowId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT w.workflowid,ws.sequence,s.specid,sb2.specname,s.unitworktime,s.setupworktime 
                        FROM workflow w
                        LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                        LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                        LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                        LEFT JOIN specbase sb2 ON sb2.specbaseid=s.specbaseid
                        WHERE  1 = 1
                        AND w.workflowid IN (" + strWorkFlowId + @")");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;

        }

        /// <summary>
        /// 获取工序派工信息
        /// </summary>
        /// <param name="para"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public DataTable GetSpecDispatchInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT di.qty,sb.specname,t.teamname,di.plannedcompletiondate,");
            strQuery.AppendLine("       di.id,di.workflowid,di.specid,di.dispatchdate,e.fullname, ");
            strQuery.AppendLine("       r.resourcename,di.containerid,di.status,");
            strQuery.AppendLine("       di.dispatchtype,DECODE(di.dispatchtype,0,'班组派工',1,'任务指派') AS disptype");
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = di.dispatchemployeeid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND di.containerid = '" + strContainerId + "'");
            }

            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND di.containerid IN (" + strConIDList + ")");
            }
            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND di.SpecID = '" + strSpecID + "'");
            }

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND di.workflowid = '" + strWorkflowID + "'");
                }
            }


            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            dt.Columns.Add("operator");
            if (dt.Rows.Count > 0)
            {
                string strID = string.Empty;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                    if (strID.Contains("'" + dt.Rows[i]["id"].ToString() + "',")==false)
                    {
                        strID += "'" + dt.Rows[i]["id"].ToString() + "',";
                    }
                }

                if (strID != "")
                {
                    strID = strID.TrimEnd(',');
                    DataTable dtOperator = GetDispatchEmployeeInfo(strID);
                    if (dtOperator.Rows.Count>0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow[] dr = dtOperator.Select("dispatchinfoid ='"+ dt.Rows[i]["id"].ToString() + "'");
                            string strOperator = string.Empty;
                            if (dr.Length>0)
                            {
                                foreach (DataRow dr1 in dr )
                                {
                                    if (strOperator.Contains(dr1["fullname"].ToString())==false)
                                    {
                                        strOperator += dr1["fullname"].ToString() + ",";
                                    }
                                }

                                if (strOperator !="")
                                {
                                    strOperator = strOperator.TrimEnd(',');
                                }
                            }

                            dt.Rows[i]["operator"] = strOperator;
                        }
                    }

                }
            }
            return dt;
        }

        /// <summary>
        /// 获取加工人员信息
        /// </summary>
        /// <param name="strDispatchinfoID"></param>
        /// <returns></returns>
        public DataTable GetDispatchEmployeeInfo(string strDispatchinfoID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT dei.dispatchinfoid,dei.employeeid,e.fullname,e.employeename
                        FROM dispatchemployeeinfo dei
                        LEFT JOIN employee e ON e.employeeid = dei.employeeid
                        WHERE dei.dispatchinfoid IN (" + strDispatchinfoID + ")");

            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 获取工序报工信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecReportInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            //报工类型
            string strReportType = string.Empty;
            if (para.Keys.Contains("ReportType"))
            {
                strReportType = (string)para["ReportType"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT w.qty,sb.specname,t.teamname,w.workreportinfoid,w.specid,w.ReportDate,e.fullname,");
            strQuery.AppendLine("       r.resourcename,e.fullname AS deName,DECODE(w.ReportType,0,'首件',3,'其他') AS rType,");
            strQuery.AppendLine("       w.containerid,w.workflowid,w.ReportType");
            strQuery.AppendLine("FROM WorkReportInfo w");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = w.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN DispatchInfo di ON di.id = w.DispatchInfoID");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = w.resourceid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("WHERE 1 = 1 ");

            //报工类型
            if (strReportType != "")
            {
                strQuery.AppendLine(" AND w.ReportType IN (" + strReportType + ") ");
            }

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND w.containerid = '" + strContainerId + "'");
            }

            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND w.containerid IN (" + strConIDList + ")");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND w.SpecID = '" + strSpecID + "'");
            }

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND di.workflowid = '" + strWorkflowID + "'");
                }
            }


            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            dt.Columns.Add("operator");
            if (dt.Rows.Count > 0)
            {
                string strID = string.Empty;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                    if (strID.Contains("'" + dt.Rows[i]["workreportinfoid"].ToString() + "',") == false)
                    {
                        strID += "'" + dt.Rows[i]["workreportinfoid"].ToString() + "',";
                    }
                }

                if (strID != "")
                {
                    strID = strID.TrimEnd(',');
                    DataTable dtOperator = GetReportemployeeinfo(strID);
                    if (dtOperator.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            DataRow[] dr = dtOperator.Select("reportinfoid ='" + dt.Rows[i]["workreportinfoid"].ToString() + "'");
                            string strOperator = string.Empty;
                            if (dr.Length > 0)
                            {
                                foreach (DataRow dr1 in dr)
                                {
                                    if (strOperator.Contains(dr1["fullname"].ToString()) == false)
                                    {
                                        strOperator += dr1["fullname"].ToString() + ",";
                                    }
                                }

                                if (strOperator != "")
                                {
                                    strOperator = strOperator.TrimEnd(',');
                                }
                            }

                            dt.Rows[i]["operator"] = strOperator;
                        }
                    }

                }
            }
            return dt;
        }

        public DataTable GetReportemployeeinfo(string strReportinfoID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"SELECT rei.*,e.fullname,e.employeename
                        FROM reportemployeeinfo rei
                        LEFT JOIN employee e ON e.employeeid = rei.employeeid
                        WHERE rei.reportinfoid IN ("+ strReportinfoID + ")");

            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 获取工序检验信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("SpecNameDisp");//检验工序
            dt.Columns.Add("reportQty");//报工数量
            dt.Columns.Add("reporter");//报工人
            dt.Columns.Add("reportdate");//报工时间
            dt.Columns.Add("EligibilityQty");//合格数量
            dt.Columns.Add("NonsenseQty");//不合格数量
            dt.Columns.Add("checktype");//检验类型
            dt.Columns.Add("checker");//检验人
            dt.Columns.Add("checkdate");//检验时间
            dt.Columns.Add("ScrapQty");//报废数
            dt.Columns.Add("containerid");//批次ID
            dt.Columns.Add("workflowid");//工艺规程ID
            dt.Columns.Add("specid");//工序ID

            //获取首检、工序检验信息
            DataTable dtCCheck = GetSpecConventionCheckInfo(para);
            if (dtCCheck.Rows.Count > 0)
            {
                for (int i = 0; i < dtCCheck.Rows.Count; i++)
                {
                    dt.Rows.Add();
                    int intAddRow = dt.Rows.Count - 1;
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["SpecNameDisp"].ToString()))
                    {
                        dt.Rows[intAddRow]["SpecNameDisp"] = dtCCheck.Rows[i]["SpecNameDisp"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["containerid"].ToString()))
                    {
                        dt.Rows[intAddRow]["containerid"] = dtCCheck.Rows[i]["containerid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["workflowid"].ToString()))
                    {
                        dt.Rows[intAddRow]["workflowid"] = dtCCheck.Rows[i]["workflowid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["specid"].ToString()))
                    {
                        dt.Rows[intAddRow]["specid"] = dtCCheck.Rows[i]["specid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["reportQty"] = dtCCheck.Rows[i]["rQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rName"].ToString()))
                    {
                        dt.Rows[intAddRow]["reporter"] = dtCCheck.Rows[i]["rName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["ReportDate"].ToString()))
                    {
                        dt.Rows[intAddRow]["reportdate"] = dtCCheck.Rows[i]["ReportDate"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["EligibilityQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["EligibilityQty"] = dtCCheck.Rows[i]["EligibilityQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["NonsenseQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["NonsenseQty"] = dtCCheck.Rows[i]["NonsenseQty"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["CheckType"].ToString()))
                    {
                        ////1=首件检验 0=常规检验 2=外协检验
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "0")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "常规检验";
                        //}
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "1")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "首件检验";
                        //}
                        //if (dtCCheck.Rows[i]["CheckType"].ToString() == "2")
                        //{
                        //    dt.Rows[intAddRow]["checktype"] = "外协检验";
                        //}

                        //0=首件检验 3=工序检验 4=入厂检验
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "0")
                        {
                            dt.Rows[intAddRow]["checktype"] = "首件检验";
                        }
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "3")
                        {
                            dt.Rows[intAddRow]["checktype"] = "工序检验";
                        }
                        if (dtCCheck.Rows[i]["CheckType"].ToString() == "4")
                        {
                            dt.Rows[intAddRow]["checktype"] = "入厂检验";
                        }
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["rcheckName"].ToString()))
                    {
                        dt.Rows[intAddRow]["checker"] = dtCCheck.Rows[i]["rcheckName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["createdate"].ToString()))
                    {
                        dt.Rows[intAddRow]["checkdate"] = dtCCheck.Rows[i]["createdate"].ToString();
                    }
                }
            }

            //获取工序终检信息
            DataTable dtSCheck = GetSpecLastCheckInfo(para);
            if (dtSCheck.Rows.Count > 0)
            {
                for (int i = 0; i < dtSCheck.Rows.Count; i++)
                {
                    dt.Rows.Add();
                    int intAddRow = dt.Rows.Count - 1;
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["SpecNameDisp"].ToString()))
                    {
                        dt.Rows[intAddRow]["SpecNameDisp"] = dtSCheck.Rows[i]["SpecNameDisp"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["containerid"].ToString()))
                    {
                        dt.Rows[intAddRow]["containerid"] = dtSCheck.Rows[i]["containerid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["workflowid"].ToString()))
                    {
                        dt.Rows[intAddRow]["workflowid"] = dtSCheck.Rows[i]["workflowid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["specid"].ToString()))
                    {
                        dt.Rows[intAddRow]["specid"] = dtSCheck.Rows[i]["specid"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["EligibilityQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["EligibilityQty"] = dtSCheck.Rows[i]["EligibilityQty"].ToString();
                    }

                    if (!string.IsNullOrEmpty(dtCCheck.Rows[i]["ScrapQty"].ToString()))
                    {
                        dt.Rows[intAddRow]["ScrapQty"] = dtCCheck.Rows[i]["ScrapQty"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["CheckType"].ToString()))
                    {
                        //  1 = 总检 0 = 工序终检 2 = 外协检验
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "0")
                        {
                            dt.Rows[intAddRow]["checktype"] = "工序终检";
                        }
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "1")
                        {
                            dt.Rows[intAddRow]["checktype"] = "总检";
                        }
                        if (dtSCheck.Rows[i]["CheckType"].ToString() == "2")
                        {
                            dt.Rows[intAddRow]["checktype"] = "外协检验";
                        }
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["rcheckName"].ToString()))
                    {
                        dt.Rows[intAddRow]["checker"] = dtSCheck.Rows[i]["rcheckName"].ToString();
                    }
                    if (!string.IsNullOrEmpty(dtSCheck.Rows[i]["createdate"].ToString()))
                    {
                        dt.Rows[intAddRow]["checkdate"] = dtSCheck.Rows[i]["createdate"].ToString();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取首检、工序检验信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecConventionCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }


            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.*,sb.specname,w.qty AS rQty,e.fullname AS rName,w.ReportDate,");
            strQuery.AppendLine("       e1.fullname AS rcheckName");
            strQuery.AppendLine("FROM ConventionCheckInfo c");
            strQuery.AppendLine("LEFT JOIN WorkReportInfo w ON w.workreportinfoid = c.ReportInfoID");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = c.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("LEFT JOIN employee e1 ON e1.employeeid = c.checkemployeeid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND c.containerid = '" + strContainerId + "'");
            }


            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND c.containerid IN (" + strConIDList + ")");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND c.SpecID = '" + strSpecID + "'");
            }

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND c.workflowid = '" + strWorkflowID + "'");
                }
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }

        /// <summary>
        /// 获取工序终检信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecLastCheckInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT sc.*,'' AS rcheckName,'' AS createdate");
            strQuery.AppendLine("FROM SpecLastCheckInfo sc");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = sc.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            //strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = w.ReportEmployeeID");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND sc.containerid = '" + strContainerId + "'");
            }


            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND sc.containerid IN (" + strConIDList + ")");
            }
            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }

        /// <summary>
        /// 获取不合格品审理信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetRejectAppInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }


            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT r.*,sb.specname");
            strQuery.AppendLine("FROM REJECTAPPINFO r");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = r.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND r.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }


            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND r.workflowid = '" + strWorkflowID + "'");
                }
            }
            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }


        /// <summary>
        /// 获取工序报废信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSpecScrapInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            StringBuilder strQuery = new StringBuilder();

            strQuery.AppendLine("SELECT sp.ScrapInfoName,e.fullname,sb.specname,spi.qty,sp.QualityRecordInfoID,");
            strQuery.AppendLine("       sp.SubmitDate,l.lossreasonname");
            strQuery.AppendLine("FROM ScrapInfo sp");
            strQuery.AppendLine("LEFT JOIN ScrapProductNoInfo spi ON spi.ScrapInfoID = sp.ID");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = sp.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = sp.SubmitEmployeeID");
            strQuery.AppendLine("LEFT JOIN LossReason l ON l.lossreasonid = spi.LossReasonID");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND sp.containerid = '" + strContainerId + "'");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }


            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND sp.workflowid = '" + strWorkflowID + "'");
                }
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            dt.Columns.Add("SpecNameDisp");
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string strName = dt.Rows[i]["SpecName"].ToString();
                    dt.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return dt;
        }


        /// <summary>
        /// 获取已保存的工序完工信息
        /// </summary>
        /// <param name="para"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public DataTable GetContainerSpecFinishInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //是否增加查询条件
            string strIsAdd = string.Empty;
            if (para.Keys.Contains("IsAdd"))
            {
                strIsAdd = (string)para["IsAdd"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*");
            strQuery.AppendLine("FROM ContainerSpecFinishInfo q");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = q.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND q.containerid IN (" + strConIDList + ")");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            }

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            if (strIsAdd != "")
            {
                if (strWorkflowID != "")
                {
                    strQuery.AppendLine(" AND sp.workflowid = '" + strWorkflowID + "'");
                }
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }


        /// <summary>
        /// 获取已保存的数据采集信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetDataCollectInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //图号ID
            string strProductID = string.Empty;
            if (para.Keys.Contains("ProductID"))
            {
                strProductID = (string)para["ProductID"];
            }

            //工艺规程ID
            string strWorkflowID = string.Empty;
            if (para.Keys.Contains("WorkflowID"))
            {
                strWorkflowID = (string)para["WorkflowID"];
            }

            //检测项信息表ID
            string strCheckItemInfoID = string.Empty;
            if (para.Keys.Contains("CheckItemInfoID"))
            {
                strCheckItemInfoID = (string)para["CheckItemInfoID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT  dc.*, dcd.productno,dcd.collectqty,dcd.checkvalue,dcd.collecttype,");
            strQuery.AppendLine("       ci.checkiteminfoname,ci.checkitem,dcd.checkitemid,dcd.SequenceNum,dcd.checkvalue1");
            strQuery.AppendLine("FROM DataCollectInfo dc");
            strQuery.AppendLine("LEFT JOIN DataCollectDetailInfo dcd ON dc.id = dcd.datacollectinfoid");
            strQuery.AppendLine("LEFT JOIN checkiteminfo ci ON ci.checkiteminfoid = dcd.checkitemid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND dc.containerid = '" + strContainerId + "'");
            }

            //工序ID
            if (strSpecID != "")
            {
                strQuery.AppendLine(" AND dc.SpecID = '" + strSpecID + "'");
            }

            //图号ID
            if (strProductID != "")
            {
                strQuery.AppendLine(" AND dc.productid = '" + strProductID + "'");
            }

            //工艺规程ID
            if (strWorkflowID != "")
            {
                strQuery.AppendLine(" AND dc.workflowid = '" + strWorkflowID + "'");
            }

            //检测项信息表ID
            if (strCheckItemInfoID != "")
            {
                strQuery.AppendLine(" AND dcd.checkitemid = '" + strCheckItemInfoID + "'");
            }

            strQuery.AppendLine("ORDER BY dcd.SequenceNum");
            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }

        /// <summary>
        /// 质量记录信息
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public DataTable GetSavedQualInfo(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();

            //跟踪卡ID
            string strContainerId = string.Empty;
            if (para.Keys.Contains("ContainerID"))
            {
                strContainerId = (string)para["ContainerID"];
            }

            string strConIDList = string.Empty;
            if (para.Keys.Contains("ContainerIDList"))
            {
                strConIDList = (string)para["ContainerIDList"];
            }

            //工序ID
            string strSpecID = string.Empty;
            if (para.Keys.Contains("SpecID"))
            {
                strSpecID = (string)para["SpecID"];
            }

            //工序名称
            string strSpecName = string.Empty;
            if (para.Keys.Contains("SpecName"))
            {
                strSpecName = (string)para["SpecName"];
            }

            //报工单ID
            string strReportInfoID = string.Empty;
            if (para.Keys.Contains("ReportInfoID"))
            {
                strReportInfoID = (string)para["ReportInfoID"];
            }

            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT q.*, DECODE(q.issubmit,0,'',1,'是') disissubmit,sb.specname,s.specrevision,");
            strQuery.AppendLine("       e.fullname");
            strQuery.AppendLine("FROM QualityRecordInfo q");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = q.submitemployeeid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = q.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("WHERE 1 = 1");

            //跟踪卡ID
            if (strContainerId != "")
            {
                strQuery.AppendLine(" AND q.containerid = '" + strContainerId + "'");
            }

            if (strConIDList != "")
            {
                strQuery.AppendLine(" AND q.containerid IN (" + strConIDList + ")");
            }

            ////工序ID
            //if (strSpecID != "")
            //{
            //    strQuery.AppendLine(" AND q.SpecID = '" + strSpecID + "'");
            //}

            //工序名称
            if (strSpecName != "")
            {
                strQuery.AppendLine(" AND sb.specname = '" + strSpecName + "'");
            }

            //报工单ID
            if (strReportInfoID != "")
            {
                strQuery.AppendLine(" AND q.ReportInfoID = '" + strReportInfoID + "'");
            }

            dt = OracleHelper.GetDataTable(strQuery.ToString());
            return dt;
        }

        #endregion

        #region 批次工艺变更
        #region 分页查询
        public uMESPagingDataDTO GetContainerMoveNonStdSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            retR.DBTable.Columns.Add("");
            retR.DBTable.Columns.Add("SpecNameDisp");
            if (retR.DBTable.Rows.Count > 0)
            {
                for (int i = 0; i < retR.DBTable.Rows.Count; i++)
                {
                    string strName = retR.DBTable.Rows[i]["SpecName"].ToString();
                    retR.DBTable.Rows[i]["SpecNameDisp"] = common.GetSpecNameWithOutProdName(strName);

                }

            }
            return retR;
        }

        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@"
                            SELECT wb.workflowname,w.workflowrevision,ch.*, wb.workflowname || ':' || w.workflowrevision AS currentFlow,
                                   ch.sequenceno AS sequence
                            FROM containeroperatehistoryinfo ch
                            LEFT JOIN container c ON c.containerid = ch.containerid
                            LEFT JOIN Workflow w ON w.workflowid = ch.workflowid
                            LEFT JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                            WHERE ch.iscurrentspec = 1 AND ch.iscurrentworkflow = 1
                            ");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(ch.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(ch.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(ch.productname) LIKE '%{0}%' OR LOWER(ch.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.planstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND ch.planstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strQuery.AppendLine("ORDER BY ch.originalstartdate DESC");

            return strQuery.ToString();
        }
        protected string GetSQL_D1(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.Append(@"
                            SELECT c.containername,pb.productname,p.description,c.qty,c.plannedstartdate,c.containerid,w1.sequence,
                                   mo.processno,mo.oprno,c.plannedcompletiondate,p.productid,c.uomid,w1.workflowid,s.specid,
                                   sb.specname,wb.workflowname,w.workflowrevision, wb.workflowname || ':' || w.workflowrevision AS currentFlow
                            FROM container c
                            LEFT JOIN product p ON p.productid = c.productid
                            LEFT JOIN currentstatus cus ON cus.currentstatusid = c.currentstatusid
                            LEFT JOIN  
                                 (SELECT w.sequence, w.workflowstepname,w.workflowid,w.workflowstepid, (CASE WHEN w.specid = '0000000000000000' 
                                       THEN (SELECT revofrcdid FROM specbase WHERE specbaseid = w.specbaseid) ELSE w.specid END) AS specid
                                  FROM workflowstep w) w1 ON w1.workflowstepid = cus.workflowstepid
                            LEFT JOIN spec s ON s.specid = w1.specid
                            LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid  
                            LEFT JOIN Workflow w ON w.workflowid = w1.workflowid
                            LEFT JOIN Workflowbase wb ON wb.workflowbaseid = w.workflowbaseid                
                            LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
                            LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid
                            WHERE c.status = 1 AND c.containername <> c.containerid
                            AND c.parentcontainerid IS NULL
                            ");

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND c.plannedstartdate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }
            strQuery.AppendLine("ORDER BY c.plannedstartdate DESC");

            return strQuery.ToString();
        }
        #endregion

        #endregion

        #region 工艺文档查看
        /// <summary>
        /// 获取工艺路线所属工艺列表
        /// </summary>
        /// <param name="strWorkflowID"></param>
        /// <returns></returns>
        public DataTable GetWorkFlowSetpsByWorkflowID(string strWorkflowID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT S.SPECID,WT.WORKFLOWSTEPID,WT.WORKFLOWSTEPNAME,WT.SEQUENCE,SSD.NORMALCYCLETIME ,
                               DECODE(WT.wipmsglabel,'子工艺','SubWorkflow','Spec') WFSTYPE
                        FROM WORKFLOWSTEP WT
                        LEFT JOIN STEPSCHEDULINGDETAIL SSD ON SSD.STEPID = WT.WORKFLOWSTEPID
                        LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WT.SPECBASEID
                        LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WT.SPECID)
                        LEFT JOIN Workflow wf ON wt.workflowid=wf.workflowid
                        ");
            sb.AppendFormat(" WHERE WT.WORKFLOWID= '{0}' AND WT.sequence IS NOT NULL ", strWorkflowID);
            sb.AppendLine("ORDER BY WT.SEQUENCE");
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 根据workflowID获取workflow
        /// </summary>
        /// <param name="workflowid"></param>
        /// <returns></returns>
        public DataTable GetWorkflowInfobyWorkflowID(string workflowid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        SELECT WF.*,wb.workflowname 
                        FROM WORKFLOW WF
                        LEFT JOIN workflowbase wb ON wb.workflowbaseid=wf.workflowbaseid
                        ");
            sb.AppendFormat("WHERE WF.WORKFLOWID='{0}'", workflowid);
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 根据产品id获取产品列表
        /// </summary>
        /// <param name="strProductID"></param>
        /// <returns></returns>
        public DataTable GetProductInfoByID(string strProductID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"       
                        SELECT P.*,PB.PRODUCTNAME,pt.producttypename
                        FROM PRODUCT P 
                        LEFT JOIN PRODUCTBASE PB ON P.PRODUCTBASEID = PB.PRODUCTBASEID
                        LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid
                        ");
            sb.AppendFormat(" WHERE P.PRODUCTID = '{0}'", strProductID);
            DataTable dt = OracleHelper.GetDataTable(sb.ToString());
            return dt;
        }

        /// <summary>
        /// 获取对象表的附件信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public DataTable GetObjectDoc(string tableName, string ID)
        {
            StringBuilder strSql = new StringBuilder();
            string value = string.Empty;
            if (tableName == "Spec")
            {
                value = ID.Split('-').Last();
            }
            else
            {
                value = ID;
            }

            DataTable dt2 = new DataTable();

            //获取关联的标准DocumentSet中的文档
            if (tableName == "Spec" || tableName == "Product")
            {
                //.获取数据库中的附件
                strSql.AppendLine("SELECT SUBSTR(doc.identifier,INSTR(doc.Identifier,'\',-1)+1) documentname,REPLACE( SUBSTR(doc.identifier,INSTR(doc.Identifier,'Temp\')),'\','/') filepath,1 type,doc.documentrevision,");
                strSql.AppendLine("      dtb2.documentname AS docName,atc.attacheddocid");
                strSql.AppendLine("  FROM " + tableName + " t ");
                strSql.AppendLine("LEFT JOIN Documententry dt ON dt.documentsetid = t.documentsetid");
                strSql.AppendLine("LEFT JOIN DOCUMENTbase dtb ON dtb.documentbaseid = dt.documentbaseid");
                strSql.AppendLine("LEFT JOIN DOCUMENT doc ON doc.documentid = NVL(dtb.revofrcdid, dt.documentid)");
                strSql.AppendLine("LEFT JOIN docattachments atm ON atm.docattachmentsid = doc.attachmentholderid");
                strSql.AppendLine("LEFT JOIN attacheddocs atc ON atc.docattachmentsid = atm.docattachmentsid");
                strSql.AppendLine("LEFT JOIN  DOCUMENTbase dtb2 ON dtb2.documentbaseid = doc.documentbaseid");
                strSql.AppendLine(" WHERE dtb2.documentname is not null and  t." + tableName + "ID='" + value + "'");
                dt2 = OracleHelper.GetDataTable(strSql.ToString());
            }

            //2.获取集成的工序关联文档
            if (tableName == "Spec")
            {
                strSql = new StringBuilder();
                strSql.AppendLine("SELECT sb.specname,s.specrevision");
                strSql.AppendLine("FROM spec s");
                strSql.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
                strSql.AppendLine("WHERE s.specid = '" + value + "'");

                DataTable specDT = OracleHelper.GetDataTable(strSql.ToString());

                string strName = specDT.Rows[0]["SpecName"].ToString();
                string strRevision = specDT.Rows[0]["SpecRevision"].ToString();

                strSql = new StringBuilder();
                strSql.AppendLine("SELECT doc.ckattachmentname AS documentname,doc.ckurl AS filepath,2 AS TYPE");
                strSql.AppendLine("FROM ckattachment doc");
                strSql.AppendLine("WHERE doc.specname = '" + strName + "' AND doc.specrevision = '" + strRevision + "'");
                DataTable dt1 = OracleHelper.GetDataTable(strSql.ToString());
                dt2.Merge(dt1);
                dt2.AcceptChanges();
            }

            //2.获取集成的产品关联文档
            if (tableName == "Product")
            {
                strSql = new StringBuilder();
                strSql.AppendLine("SELECT pb.productname,p.productrevision");
                strSql.AppendLine("FROM product p");
                strSql.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
                strSql.AppendLine("WHERE p.productid = '" + value + "'");

                DataTable specDT = OracleHelper.GetDataTable(strSql.ToString());

                string strName = specDT.Rows[0]["ProductName"].ToString();
                string strRevision = specDT.Rows[0]["ProductRevision"].ToString();

                strSql = new StringBuilder();
                strSql.AppendLine("SELECT doc.ckattachmentname AS documentname,doc.ckurl AS filepath,2 AS TYPE");
                strSql.AppendLine("FROM ckattachment doc");
                strSql.AppendLine("WHERE doc.productname = '" + strName + "' AND doc.productrev = '" + strRevision + "'");
                DataTable dt1 = OracleHelper.GetDataTable(strSql.ToString());
                dt2.Merge(dt1);
                dt2.AcceptChanges();
            }

            if (tableName == "Workflow")
            {
                strSql = new StringBuilder();
                strSql.AppendLine("SELECT doc.ckattachmentname AS documentname,doc.ckurl AS filepath,2 AS TYPE");
                strSql.AppendLine("FROM ckattachment doc");
                strSql.AppendLine("WHERE doc.workflowid = '" + value + "'");
                DataTable dt1 = OracleHelper.GetDataTable(strSql.ToString());
                dt2.Merge(dt1);
                dt2.AcceptChanges();
            }

            if (tableName == "Container") {
                strSql = new StringBuilder();
                strSql.AppendLine("SELECT doc.ckattachmentname AS documentname,doc.ckurl AS filepath,2 AS TYPE");
                strSql.AppendLine("FROM ckattachment doc");
                strSql.AppendLine("WHERE doc.containerid = '" + value + "'");
                DataTable dt1 = OracleHelper.GetDataTable(strSql.ToString());
                dt2.Merge(dt1);
                dt2.AcceptChanges();
            }

            if (dt2.Rows.Count > 0)
            {
                for (int i = 0; i < dt2.Rows.Count; i++)
                {
                    string strName = string.Empty;
                    if (string.IsNullOrEmpty(dt2.Rows[i]["documentname"].ToString()))
                    {
                        if (!string.IsNullOrEmpty(dt2.Rows[i]["filepath"].ToString()))
                        {
                            string[] array = (dt2.Rows[i]["filepath"].ToString()).Split('/');
                            if (array.Length > 0)
                            {
                                dt2.Rows[i]["documentname"] = array[array.Length - 1];
                            }
                        }
                    }
                }
            }

            return dt2;
        }
        #endregion

        #region 保存数据
        public bool SaveDataToDatabase(Dictionary<string, object> para, out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {

                        //表名称
                        string strTableName = ds.Tables[i].TableName;
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;
                        //主键值
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strKey + "= :" + strKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1];
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strKey;
                        DelParam1[0].OracleDbType = OracleDbType.Int32;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;

                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j = 0; j < dtMain.Rows.Count; j++)
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h = 0; h < dtMain.Columns.Count; h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" +
                                                        strMainColumn + ")" +
                                                        " VALUES(" +
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k = 0; k < dtMain.Columns.Count; k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }



                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "入库完成！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }


            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }

        public bool SaveDataToDatabaseNew(Dictionary<string, object> para, out string strMessage)
        {
            bool bReturn = true;
            strMessage = "";
            DataSet ds = new DataSet();
            if (para.Keys.Contains("dsData"))
            {
                ds = (DataSet)para["dsData"];
            }
            try
            {
                if (ds.Tables.Count > 0)
                {
                    Dictionary<string, OracleParameter[]> SQLlist = new Dictionary<string, OracleParameter[]>();
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {

                        //表名称
                        string[] strTableNameList = ds.Tables[i].TableName.Split('㊣');
                        string strTableName = strTableNameList[0];
                        string strDelKey = strTableNameList[1];
                        DataTable dtMain = ds.Tables[i];
                        //主键名称
                        string strKey = ds.Tables[i].Columns[0].ColumnName;

                        //删除表
                        string[] strKeyValueList = ds.Tables[i].Rows[ds.Tables[i].Rows.Count - 1][strDelKey].ToString().Split('㊣');
                        string strKeyValue = strKeyValueList[0];

                        string strDelSql = "DELETE FROM " + strTableName + " m" +
                                           " WHERE " + strDelKey + "= :" + strDelKey;

                        OracleParameter[] DelParam1 = new OracleParameter[1];
                        DelParam1[0] = new OracleParameter();
                        DelParam1[0].ParameterName = ":" + strDelKey;
                        DelParam1[0].OracleDbType = OracleDbType.Varchar2;
                        DelParam1[0].Direction = ParameterDirection.Input;
                        DelParam1[0].Value = strKeyValue;

                        //'添加到哈希表
                        SQLlist.Add(strDelSql, DelParam1);

                        for (int j = 0; j < dtMain.Rows.Count - 1; j++)
                        {
                            //表列名
                            string strMainColumn = string.Empty;
                            string strMainColumnList = string.Empty;
                            for (int h = 0; h < dtMain.Columns.Count; h++)
                            {
                                string strName = dtMain.Columns[h].ColumnName.ToString();
                                if (strMainColumn.Contains("," + strName))
                                { }
                                else
                                {
                                    strMainColumn += "," + strName;
                                    if (strName.ToLower() == strKey.ToLower())
                                    { strName += j.ToString(); }
                                    strMainColumnList += "," + ":" + strName;
                                }
                            }
                            if (strMainColumn != "")
                            {
                                strMainColumn = strMainColumn.TrimStart(',');
                                strMainColumnList = strMainColumnList.TrimStart(',');

                                string strInsertSQL1 = "INSERT INTO " + strTableName + " (" +
                                                        strMainColumn + ")" +
                                                        " VALUES(" +
                                                        strMainColumnList + ")";
                                OracleParameter[] param = new OracleParameter[dtMain.Columns.Count];

                                for (int k = 0; k < dtMain.Columns.Count; k++)
                                {

                                    //参数名称
                                    string strParameterName = string.Empty;
                                    strParameterName = dtMain.Columns[k].ColumnName;
                                    if (strParameterName.ToLower() == strKey.ToLower())
                                    {
                                        strParameterName += j.ToString();
                                    }

                                    strParameterName = ":" + strParameterName;
                                    //行内容
                                    string strRowContent = string.Empty;
                                    if (!string.IsNullOrEmpty(dtMain.Rows[j][k].ToString()))
                                    {
                                        strRowContent = dtMain.Rows[j][k].ToString();
                                    }


                                    //保存值
                                    string strValue = string.Empty;

                                    //类型
                                    string strType = string.Empty;
                                    if (strRowContent != "")
                                    {
                                        string[] array = strRowContent.Split('㊣');
                                        strValue = array[0];
                                        strType = array[1];
                                    }

                                    if (strType == "Date")
                                    {
                                        DateTime operDate = new DateTime();
                                        if (strValue != "")
                                        {
                                            operDate = Convert.ToDateTime(strValue);
                                        }

                                        param[k] = new OracleParameter();

                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Date;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = operDate;
                                    }

                                    else if (strType == "Integer")
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Int32;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }

                                    else
                                    {
                                        param[k] = new OracleParameter();
                                        param[k].ParameterName = strParameterName;
                                        param[k].OracleDbType = OracleDbType.Varchar2;
                                        param[k].Direction = ParameterDirection.Input;
                                        param[k].Value = strValue;
                                    }



                                }
                                //添加到哈希表
                                SQLlist.Add(strInsertSQL1, param);
                            }

                        }

                    }
                    OracleHelper.ExecuteSqlTranByHash(SQLlist);
                    strMessage = "保存成功！";
                }
                else
                {
                    strMessage = "没有要保存的信息！";
                    bReturn = false;
                    return bReturn;
                }


            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                bReturn = false;
            }
            return bReturn;
        }
        #endregion

        #region 更新数据
        public int UpdateDataToDatabase(DataTable dt, out string strMessage)
        {
            int iReturn = -1;
            strMessage = string.Empty;
            try
            {
                if (dt.Rows.Count > 0)
                {
                    string strTableName = dt.Rows[0][0].ToString();
                    string strKey = dt.Columns[1].ColumnName.ToString();
                    string[] strKeyValueList = dt.Rows[0][1].ToString().Split('㊣');
                    string strKeyValue = strKeyValueList[0];
                    StringBuilder strSql = new StringBuilder();
                    strSql.AppendLine("UPDATE " + strTableName);
                    strSql.AppendLine(" SET");
                    for (int i = 1; i < dt.Columns.Count; i++)
                    {
                        string strColName = dt.Columns[i].ColumnName;

                        //行内容
                        string strRowContent = string.Empty;
                        if (!string.IsNullOrEmpty(dt.Rows[0][i].ToString()))
                        {
                            strRowContent = dt.Rows[0][i].ToString();
                        }


                        //保存值
                        string strValue = string.Empty;

                        //类型
                        string strType = string.Empty;
                        if (strRowContent != "")
                        {
                            string[] array = strRowContent.Split('㊣');
                            strValue = array[0];
                            strType = array[1];
                        }

                        if (strType == "Date")
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS')", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", strValue));
                            }
                        }
                        else
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}'", strValue));
                            }
                            else
                            {
                                strSql.AppendLine(string.Format(strColName + " = '{0}',", strValue));
                            }
                        }
                    }

                    strSql.AppendLine(string.Format("WHERE " + strKey + " ='{0}'", strKeyValue));
                    iReturn = OracleHelper.ExecuteSql(strSql.ToString());
                }

            }
            catch (Exception ex)
            {
                strMessage = ex.Message;
                iReturn = -1;
            }
            return iReturn;
        }
        #endregion

    }
}
